"""
Case Type   : 多块预读
Case Name   : 开启预读块，create table/view as select
Create At   : 2024/12/04
Owner       : @li-xin12345
Description :
    1、建表，重启
    2、复制表时扫描表
    3、创建视图, 不扫描表数据
    4、创建物化视图，全表扫描
    5、清理环境
Expect      :
    1、成功
    2、成功
    3、预读了设置的块数量
    4、成功
    5、成功
History     :
"""
import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class SessionTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.common = Common()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user_node = Node('PrimaryDbUser')
        self.tb_name = 't_multi_bulk_read_0016'
        self.view_name = 'v_multi_bulk_read_0016'
        self.pg_log = \
            os.path.join(macro.PG_LOG_PATH, macro.DN_NODE_NAME.split('/')[0])

    def test_multi_bulk_read(self):
        text = '----step1: 建表，重启；expect: 成功----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name}_1 cascade;
        create table {self.tb_name}_1 (
            o_orderkey      bigint       not null,
            o_custkey       integer      not null,
            o_orderstatus   char(1)      not null,
            o_totalprice    decimal(15,2) not null,
            o_orderdate     date         not null,
            o_orderpriority char(15)     not null,
            o_clerk         char(15)     not null,
            o_shippriority  integer      not null,
            o_comment       varchar(79)  not null
        );
        insert into {self.tb_name}_1 values
        (599999943, 454411, 'O', 105846.51, '1996-03-31', '3-MEDIUM', 
        'Clerk#000070424', 0, 
        'olites. ironic foxes sleep doggedly. unusual, bold instructions');
        insert into {self.tb_name}_1 values
        (599999968, 2271617, 'O', 181129.56, '1995-12-14', '4-NOT SPECIFIED', 
        'Clerk#000099834', 0, 'equests are blithely. final deposits are iron');
        insert into {self.tb_name}_1 values
        (599999969, 4063478, 'F', 93255.68, '1994-04-20', '1-URGENT', 
        'Clerk#000008803', 0, 'blithely above the accounts. blithely fina');
        insert into {self.tb_name}_1 values
        (599999971, 13602718, 'F', 24734.96, '1992-02-18', '2-HIGH', 
        'Clerk#000042332', 0, 
        's warhorses after the even, even deposits slyly above the ironic de');
        insert into {self.tb_name}_1 values
        (599999972, 5864876, 'F', 164253.17, '1994-03-09', '2-HIGH', 
        'Clerk#000071086', 0, 
        'ly requests. quickly regular requests cajole upon the quickly ironic');
        insert into {self.tb_name}_1 values
        (599999973, 5956910, 'O', 314459.45, '1996-03-04', '3-MEDIUM', 
        'Clerk#000039173', 0, 'st among the furiously final packages.');
        insert into {self.tb_name}_1 values
        (599999974, 7780136, 'O', 117452.54, '1997-06-24', '3-MEDIUM', 
        'Clerk#000073893', 0, 'equests. carefully special requests ideas. s');
        insert into {self.tb_name}_1 values
        (599999975, 5730910, 'F', 374027.19, '1994-04-05', '2-HIGH', 
        'Clerk#000003369', 0, 'oost. regular boost furiously. doggedly regul');
        insert into {self.tb_name}_1 values
        (600000000, 4400869, 'O', 80002.60, '1997-12-02', '4-NOT SPECIFIED', 
        'Clerk#000019490', 0, 's. slyly final after the fluffily regular');
        insert into {self.tb_name}_1 values
        (generate_series(2, 100000, 2), 14864260, 'F', 109713.24, '1993-10-19',
         '2-HIGH', 'Clerk#000050083', 0, 'sual requests. pending, fi');
        '''
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS,
                      res, f'执行失败：{text}')
        self.assertEqual(res.count('INSERT 0 1'), 9, f'执行失败：{text}')
        self.assertIn('INSERT 0 50000', res, f'执行失败：{text}')

        msg = self.pri_sh.restart_db_cluster()
        self.log.info(msg)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)

        text = '----step2: 复制表时扫描表；expect: 成功----'
        self.log.info(text)
        sql = f"set heap_bulk_read_size = 16;" \
              f"drop table if exists {self.tb_name}_2;" \
              f"create table {self.tb_name}_2 as " \
              f"select * from {self.tb_name}_1;"
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.SET_SUCCESS_MSG, res, f'执行失败：{text}')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.assertIn('INSERT 0 50009', res, f'执行失败：{text}')

        text = '----step3: 创建视图, 不扫描表数据；expect: 成功----'
        self.log.info(text)
        sql = f"set heap_bulk_read_size = 8;" \
              f"drop view if exists {self.view_name}_1;" \
              f"create view {self.view_name}_1 as " \
              f"select * from {self.tb_name}_1 union " \
              f"select * from {self.tb_name}_2;"
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.SET_SUCCESS_MSG, res, f'执行失败：{text}')
        self.assertIn(self.constant.DROP_VIEW_SUCCESS_MSG, res,
                      f'执行失败：{text}')
        self.assertIn(self.constant.CREATE_VIEW_SUCCESS_MSG, res,
                      f'执行失败：{text}')

        msg = self.pri_sh.restart_db_cluster()
        self.log.info(msg)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)

        text = '----step4: 创建物化视图，全表扫描；expect: 成功----'
        self.log.info(text)
        sql = f"drop view if exists {self.view_name}_2;" \
              f"create materialized view {self.view_name}_2 as " \
              f"select * from {self.tb_name}_1;"
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_VIEW_SUCCESS_MSG, res,
                      f'执行失败：{text}')
        self.assertIn('CREATE MATERIALIZED VIEW', res, f'执行失败：{text}')

        self.log.info('--查看日志中打印的预读块数量；expect: 预读了设置的块数量--')
        cmd = f"cd {self.pg_log};" \
              f"ls -t | head -1 | awk '{{{{print $1}}}}' | " \
              f"xargs cat | tail -30 | " \
              f"grep 'End of pre-Read, the max blocks batch is'"
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_user_node, cmd)
        self.log.info(res)
        self.assertIsNotNone(res, f'执行失败：{text}')

    def tearDown(self):
        text = '----step5: 清理环境;expect:成功----'
        self.log.info(text)
        sql = f"drop table if exists {self.tb_name}_1, " \
              f"{self.tb_name}_2 cascade;"
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
